from bs4 import BeautifulSoup
import openpyxl
from collections import OrderedDict
from openpyxl.utils import get_column_letter
import os


def get_last_row_title(ws, row_index_):
    temp = []
    for cell_ in ws[row_index_]:
        temp.append(cell_.value)
    # print(temp)
    element_counts = {}  # 创建一个空字典，用于存储元素出现的个数

    for element in temp:
        if element in element_counts:
            element_counts[element] += 1  # 元素已存在于字典中，增加计数
        else:
            element_counts[element] = 1  # 元素不存在于字典中，初始化计数为1
    # print(element_counts.values())

    accumulated_lst = []
    total = 0
    for num in element_counts.values():
        total += num
        accumulated_lst.append(total)
    # print(accumulated_lst)
    prev_second = 0
    value_pairs = []
    for num in accumulated_lst:
        value_pairs.append((prev_second + 1, num))
        prev_second = num
    # print(value_pairs)
    return value_pairs


def write2txt(filepath, string):
    txt_filename = filepath.rsplit('.', 1)[0] + ".txt"

    with open(txt_filename, 'w') as file:
        file.write(string)


def process_html_table(filename, num_row_title):
    wb = openpyxl.Workbook()
    ws = wb.active
    with open(filename, 'r') as f:
        html_content = f.read()
    soup = BeautifulSoup(html_content, 'html.parser')
    table = soup.find('table')
    table_data = []
    for row in table.find_all('tr'):
        row_data = []
        # 遍历表格单元格
        for cell in row.find_all(['td']):
            rowspan = int(cell.get('rowspan', 1))
            colspan = int(cell.get('colspan', 1))
            cell_text = cell.get_text().strip()

            # 添加单元格数据到行数据列表
            row_data.append({
                'text': cell_text,
                'rowspan': rowspan,
                'colspan': colspan
            })

        # 添加行数据到表格数据列表
        table_data.append(row_data)

    # 填充合并单元格
    for row_index, row_data in enumerate(table_data):
        for col_index, cell_data in enumerate(row_data):
            temp = 0
            cell_text = cell_data['text']
            rowspan = cell_data['rowspan']
            colspan = cell_data['colspan']
            # 设置合并单元格的值
            for i in range(rowspan):
                for j in range(colspan):
                    current_row = row_index + i + 1
                    current_col = col_index + j + 1

                    # 如果当前单元格是合并单元格的起始单元格，则填充值
                    while ws.cell(row=current_row, column=current_col).value is not None:
                        current_col = current_col + 1
                        moved_col = current_col
                        temp = 1

                    if temp == 1:
                        current_col = moved_col
                    if i == 0 and j == 0:
                        ws.cell(row=current_row, column=current_col, value=cell_text)
                    # 如果当前单元格不是合并单元格的起始单元格，则复制合并单元格的值
                    else:
                        ws.cell(row=current_row, column=current_col, value=cell_text)

    # print('------------------------------------------------------------------')
    # for row in ws.iter_rows(values_only=True):
    #     # 打印每一行的值
    #     print("\t".join(str(cell) for cell in row))
    # print('------------------------------------------------------------------')

    # print('row_title_list', row_title_list)
    if num_row_title > 0:
        last_row_pairs = get_last_row_title(ws, num_row_title)
        # print(last_row_pairs)

        for value_pair in last_row_pairs:
            first_value = value_pair[0]
            last_value = value_pair[1]
            # 合并单元格
            column1_letter = get_column_letter(first_value)
            column2_letter = get_column_letter(last_value)
            # del_letter = get_column_letter(first_value+1)

            for row in range(num_row_title + 1, ws.max_row + 1):
                if column1_letter == column2_letter:
                    break
                cell1 = ws[f"{column1_letter}{row}"]
                cell2 = ws[f"{column2_letter}{row}"]

                # 拼接两个单元格的值
                if cell1.value != cell2.value:
                    merged_value = f"{cell1.value}{cell2.value}"

                    # 合并单元格
                    merged_range = f"{cell1.coordinate}:{cell2.coordinate}"
                    ws.merge_cells(merged_range)

                    # 设置合并后的单元格值
                    merged_cell = ws[cell1.coordinate]
                    merged_cell.value = merged_value
            # print('------------------------------------------------------------------')
            # for row in ws.iter_rows(values_only=True):
            #     # 打印每一行的值
            #     print("\t".join(str(cell) for cell in row))
            # print('------------------------------------------------------------------')

            if column1_letter == column2_letter:
                break
            for i in range(first_value + 1, last_value + 1):
                ws.delete_cols(i)

        # print('------------------------------------------------------------------')
        # for row in ws.iter_rows(values_only=True):
        #     # 打印每一行的值
        #     print("\t".join(str(cell) for cell in row))
        # print('------------------------------------------------------------------')

        row_title_list = []
        for col in ws.iter_cols(min_row=1, max_row=num_row_title):
            temp_list = []
            for cell in col:
                temp_list.append(cell.value)
            row_title_list.append(temp_list)
        # print('row_title_list', row_title_list)
        row_title_list = [list(OrderedDict.fromkeys(lst)) for lst in row_title_list]
        # print('row_title_list', row_title_list)
        row_title_list = [''.join(map(str, sublist)) for sublist in row_title_list]
        row_title_list = [element + '为' for element in row_title_list]

        information_Of_table = ''

        for row in ws.iter_rows(min_row=num_row_title + 1, min_col=1):
            temp_list = []
            result = []
            for cell in row:
                # print(cell.value)
                temp_list.append(cell.value)
                result = ','.join([str(x) + str(y) for x, y in zip(row_title_list, temp_list)])
                if result[-1] in [',', '。']:
                    result = result[:-1]
                # print('result',result)
            # if result.endswith('。'):
            #     information_Of_table += result
            # else:
            #     information_Of_table += result + '。'
            information_Of_table += result + '。'
        print(information_Of_table)

        write2txt(filename,information_Of_table)

    if num_row_title == 0:
        temp = []
        information_Of_table = ''
        for row in ws.iter_rows(values_only=True):
            temp.append(list(row))

        for sub in temp:
            # print(sub)
            # merged_list = [element for sublist in sub for element in sublist]
            merged_list = list(OrderedDict.fromkeys(sub))
            merged_list[-1] = '为' + merged_list[-1]
            result = ','.join(merged_list)
            information_Of_table += result + '。'
        print(information_Of_table)
        write2txt(filename, information_Of_table)


